#
#  F-Spot Plugin for XBMC
#  Copyright (C) 2009 forteresce@gmail.com
#
#  This program is free software; you can redistribute it and/or
#  modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2
#  of the License, or (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
#
# Thanks to Anoop Menon for the iPhoto plugin for xbmc.

__scriptname__ = "F-Spot Plugin for XBMC"
__author__     = "forteresce@gmail.com"
__url__        = "http://code.google.com/p/xbmc-fspot/"
__svn_url__    = "http://xbmc-fspot.googlecode.com/svn/trunk/"
__credits__    = "a lone leaf"
__version__    = "[Beta 1.0]"
__license__    = "GPL v2"

try:
    from pysqlite2 import dbapi2 as sqlite
except:
    print 'ERROR: This program requires pysqlite2', 'http://initd.org/tracker/pysqlite/'
from urllib import unquote
import sys
import os
import os.path

class FSpotDB:
    def __init__(self, dbfile):
        self.dbconn = sqlite.connect(dbfile)
        self.InitDB()
        return

    def InitDB(self):
        try:
            self.dbconn.execute("PRAGMA synchronous = OFF")
            self.dbconn.execute("PRAGMA default_synchronous = OFF")
            self.dbconn.execute("PRAGMA journal_mode = OFF")
            self.dbconn.execute("PRAGMA temp_store = MEMORY")
            self.dbconn.execute("PRAGMA encoding = \"UTF-8\"")
        except Exception, e:
            print str(e)
            print traceback.print_exc()
            pass

    def getCategoriesUnder(self, parentId):
        categories = []
        try:
            self._debug("Getting Categories Under: " + str(parentId))
            cur = self.dbconn.cursor()
            cur.execute("""SELECT 
                                C.id, C.name, C.icon
                           FROM 
                                tags C
                           WHERE 
                                C.category_id=?
                                and C.is_category=1""", (parentId,))

            for tuple in cur:
                self._debug("Category: " + str(tuple))
                categories.append(tuple)
        except Exception, e:
            print str(e)
            pass
        return categories

    def getPhotosInCategory(self, category):
        photos = []
        try:
            self._debug("Getting Photos Under: " + str(category))
            cur = self.dbconn.cursor()
            
            if category != "-1":
                cur.execute("""SELECT
                                P.id, P.base_uri, P.filename 
                           FROM 
                                photos P
                           WHERE 
                                P.id in 
                                (
                                    SELECT photo_id FROM photo_tags WHERE tag_id = ?
                                )""", (category,))
            else:
                cur.execute("""SELECT
                                P.id, P.base_uri, P.filename 
                           FROM 
                                photos P
                           WHERE 
                                P.id not in 
                                (
                                    SELECT photo_id FROM photo_tags
                                )""")

            for tuple in cur:
                self._debug("Photo: " + str(tuple))
                photos.append(tuple)
        except Exception, e:
            print str(e)
            pass
        return photos

    def getYears(self):
        years = []
        try:
            self._debug("Getting Years")
            cur = self.dbconn.cursor()
            cur.execute("""SELECT
                            distinct strftime('%Y', P.time, 'unixepoch')
                       FROM 
                            photos P""")

            for tuple in cur:
                years.append(tuple)
        except Exception, e:
            print str(e)
            pass
        return years

    def getMonths(self, year):
        months = []
        try:
            self._debug("Getting Months for %s" % year)
            cur = self.dbconn.cursor()
            cur.execute("""SELECT
                            distinct strftime('%m', P.time, 'unixepoch')
                       FROM 
                            photos P
                       WHERE
                            strftime('%Y', P.time, 'unixepoch') = ?""", (year,))

            for tuple in cur:
                months.append(tuple)
        except Exception, e:
            print str(e)
            pass
        return months

    def getDays(self, year, month):
        days = []
        try:
            self._debug("Getting Days for %s-%s" % (year, month))
            cur = self.dbconn.cursor()
            cur.execute("""SELECT
                            distinct strftime('%d', P.time, 'unixepoch')
                       FROM 
                            photos P
                       WHERE
                            strftime('%Y', P.time, 'unixepoch') = ?
                            and strftime('%m', P.time, 'unixepoch') = ?""", (year,month,))

            for tuple in cur:
                days.append(tuple)
        except Exception, e:
            print str(e)
            pass
        return days

    def getPhotosOnDate(self, year, month, day):
        dateString = "%s-%s-%s" % (year, month, day)
        photos = []
        try:
            self._debug("Getting Photos For Date: " + dateString)
            cur = self.dbconn.cursor()
            cur.execute("""SELECT
                            P.id, P.base_uri, P.filename 
                       FROM
                            photos P
                       WHERE 
                            date(P.time, 'unixepoch') = ?""", (dateString,))

            for tuple in cur:
                self._debug("Photo: " + str(tuple))
                photos.append(tuple)
        except Exception, e:
            print str(e)
            pass
        return photos
        
    def _debug(self, msg):
        print ("F-Spot: " + msg)

    def _error(self, msg):
        print ("ERROR F-Spot: " + msg)
